package com.cf.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;

import java.util.LinkedHashMap;
import java.util.List;

@Mapper
public interface BigDataMapper {
    @Select("SELECT U_Division Division,cast(sum(DocTotal)/10000 as decimal(18,2)) Total  "
            + "FROM ORDR  "
            + "WHERE DocDate BETWEEN '2020-08-01' AND '2021-01-31' "
            + "GROUP BY U_Division  ")
    List<LinkedHashMap<String, Object>> division();

    @Select("{CALL testPrd(#{yearly,mode=IN})}")
    @Options(statementType = StatementType.CALLABLE)
    String test(@Param("yearly") Integer yearly);

    @Select("SELECT SUM(DOCTOTAL/10000) total  FROM ODLN   "
            + " WHERE YEAR(DOCDATE)=#{yearly} AND MONTH(DOCDATE)<=#{monthly} ")
    Double totalDeliveryToday(Integer yearly, Integer monthly);

    @Select("SELECT SUM(DOCTOTAL/10000) total  FROM ORDR   "
            + " WHERE YEAR(DOCDATE)=#{yearly} AND MONTH(DOCDATE)<=#{monthly} ")
    Double totalOrderToday(Integer yearly, Integer monthly);

    @Select("SELECT SUM(DOCTOTAL/10000) total  FROM ODLN   "
            + " WHERE YEAR(DOCDATE)=#{yearly}   ")
    Double totalDeliveryYearly(Integer yearly);

    @Select("SELECT SUM(DOCTOTAL/10000) total  FROM ORDR   "
            + " WHERE YEAR(DOCDATE)=#{yearly} ")
    Double totalOrderYearly(Integer yearly);

    @Select("SELECT SUM(DOCTOTAL/10000)*2.67 total,U_T039 division  FROM ODLN "
            + "WHERE YEAR(DOCDATE)=#{yearly} AND MONTH(DOCDATE)<=#{monthly} "
            + "group by U_T039 ")
    List<LinkedHashMap<String, Object>> divisionDelivery(Integer yearly,
                                                         Integer monthly);

    // 按部门、产品大类
    @Select("SELECT SUM(DOCTOTAL/10000)*2.67 total,U_T039 division  FROM ORDR "
            + "WHERE YEAR(DOCDATE)=#{yearly} AND MONTH(DOCDATE)<=#{monthly} "
            + "group by U_T039 ")
    List<LinkedHashMap<String, Object>> divisionOrder(Integer yearly,
                                                      Integer monthly);

    // 折算成人民币，price和priceafvat 都是原币价格
    @Select("SELECT SUM(T0.OpenQty*T0.LineTotal/T0.Quantity*T0.PriceAfVAT/(case when T0.Price=0 then 1 else T0.Price end ))/10000  NFTOTAL "
            + "FROM RDR1 T0  "
            + "INNER JOIN ORDR T1 ON T0.DocEntry =T1.DocEntry    "
            + "WHERE T0.OpenQty>0 AND T1.DOCSTATUS<>'C' AND T1.DocManClsd='N' ")
    Double NotDelivery();

    // 合同分析 and u_t039='甲板'
    @Select("select YEAR(docDate) yearly,convert(decimal(18,2),SUM(doctotal)/10000) total , "
            + "COUNT( distinct left(NumAtCard,9))*2.67 contractQty, "
            + "convert(decimal(18,2),SUM(doctotal)/COUNT( distinct left(NumAtCard,9))/10000) contractAvg "
            + "from ORDR   WHERE YEAR(docDate) between 2014 and #{yearly} "
            + "and NumAtCard not like '%取消%'  group by YEAR(docDate) "
            + "order by YEAR(docDate)")
    List<LinkedHashMap<String, Object>> contract(Integer yearly);

    // 客户数
    @Select("SELECT COUNT(distinct  CardName) countCard " + "FROM ORDR  "
            + "WHERE YEAR(docDate) between #{startYear} and #{endYear}")
    Integer cardQuantity(Integer startYear, Integer endYear);

    // 业务员
    @Select("SELECT  SUM(T0.DocTotal/10000) total  , LTRIM(dbo.splitfield('-',T2.SlpName)) slpName  ,year(T0.DocDate) yearly "
            + "FROM ODLN  T0 " + "INNER JOIN OSLP T2 ON T0.SlpCode=T2.SlpCode  "
            + "where year(T0.DocDate)=#{yearly}  AND T0.CANCELED='N' "
            + "group by dbo.splitfield('-',T2.SlpName),year(T0.DocDate)  "
            + "having SUM(T0.DocTotal/10000)>50")
    List<LinkedHashMap<String, Object>> salesMan(Integer yearly);

    // 个人考勤明细
    @Select("{CALL CharliePrdAttendanceDetail}")
    @Options(statementType = StatementType.CALLABLE)
    List<LinkedHashMap<String, Object>> checkAttendanceDetail();

    // 个人考勤汇总
    @Select("{CALL CharliePrdAttendanceSum(#{name,mode=IN})}")
    @Options(statementType = StatementType.CALLABLE)
    LinkedHashMap<String, Object> checkAttendanceSum(
            @Param("name") String name);

}
